package com.hxwm.hwdbtool.service;

import com.hxwm.hwdbtool.entity.dbmigration.MapperField;
import com.hxwm.hwdbtool.entity.dbmigration.MapperSheet;
import com.hxwm.hwdbtool.entity.dbmigration.MapperTable;
import com.hxwm.hwdbtool.entity.dbmigration.MapperTableRelation;
import com.hxwm.hwdbtool.util.HwUtil;
import org.apache.log4j.Logger;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.nio.charset.Charset;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

public class GenerateScriptService {
    private Logger logger = Logger.getLogger(GenerateScriptService.class);
    private int LIMIT_QUERY_NUM = 50000;
    private MapperRuleService mapperRuleService = new MapperRuleService();
    /**
     * 多对一映射，数据来源不同
     * @param statement1
     * @param statement2
     * @param mapperSheet
     * @param outputDirectory
     * @throws Exception
     */
    public void generateMultiSqlFile(Statement statement1, Statement statement2, MapperSheet mapperSheet, String outputDirectory, Charset exportCharacterSet) throws Exception {
        List<MapperTable> oldTableList = mapperSheet.getOldTableList();
        MapperTable newTable = mapperSheet.getNewTable();
        String whereCondition = mapperSheet.getWhereCondition();
        String newTableFieldSql = mapperSheet.getNewTableFieldSql();
        List<MapperTableRelation> tableRelationList = mapperSheet.getTableRelationList();

        //查询旧表的全字段数据
        String selectQuery1 = "SELECT * FROM " + oldTableList.get(0).getTableName() ;
        String selectQuery2 = "SELECT * FROM " + oldTableList.get(1).getTableName() ;

        logger.info("数据库查询语句1：\n" + selectQuery1);
        logger.info("数据库查询语句2：\n" + selectQuery2);

        // 执行查询旧表数据，生成插入SQL
        ResultSet resultSet1 = statement1.executeQuery(selectQuery1);
        ResultSet resultSet2 = statement2.executeQuery(selectQuery2);
        Map<String, String> whereConditionMap = HwUtil.parseWhereClause(whereCondition);

        // 创建SQL脚本文件
        String scriptFileName = newTable.getTableName() + "_script.sql";
        File scriptFile = new File(outputDirectory, scriptFileName);

        // 拼接sql
        String insertSQL = "INSERT INTO " + newTable.getTableName() + " (" + newTableFieldSql + ") VALUES (";

        try(BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(scriptFile), exportCharacterSet))){
            // 进行笛卡尔积运算并使用条件过滤
            while (resultSet1.next()) {
                while (resultSet2.next()) {
                    // 获取 resultSet1 和 resultSet2 中的列值,使用where条件过滤
                    boolean flag = true;//判断是否满足where条件的标志
                    for (Map.Entry<String,String> entry:whereConditionMap.entrySet()){
                        String oldValue1 = resultSet1.getString(entry.getKey());
                        String oldValue2 = resultSet2.getString(entry.getValue());
                        if(!oldValue1.equals(oldValue2)){
                            flag = false;
                            break;
                        }
                    }
                    if(flag){//满足条件的数据直接写入文件
                        String values = "";
                        String multiValues = "";
                        boolean multiFlag = false;
                        for (MapperTableRelation mapperTableRelation : tableRelationList) {
                            MapperField newField = mapperTableRelation.getNewField();
                            String oldTableIndex = mapperTableRelation.getOldTableIndex();

                            String specialCondition = newField.getSpecialCondition();
                            String relRelation = newField.getRelRelation();

                            if(HwUtil.isNullOrEmpty(newField.getFieldName())){
                                continue;
                            }

                            //特殊域优先级最高
                            if(!HwUtil.isNullOrEmpty(specialCondition)){
                                if("NOW".equals(specialCondition)){
                                    values = values + "NOW(),";
                                }else if("GLOBAL".equals(specialCondition)){
                                    values = values + "'" + HwUtil.generateGlobalFlow() + "',";
                                } else if (!HwUtil.isNullOrEmpty(specialCondition)){
                                    values = values + "'" + specialCondition + "',";
                                }
                                continue;
                            }
                            //关联关系赋值优先级其次

                            //值为空的赋值为null
                            String oldValue;
                            if("0".equals(oldTableIndex)){//0-第一个老表，1-第二个老表
                                oldValue = resultSet1.getString(mapperTableRelation.getOldTableFieldName());
                            }else{
                                oldValue = resultSet2.getString(mapperTableRelation.getOldTableFieldName());
                            }
                            String fieldType = newField.getFieldType().toLowerCase();

                            if (oldValue == null) {
                                if(newField.getEnumMapper() != null && !newField.getEnumMapper().isEmpty()){
                                    oldValue = (String) newField.getEnumMapper().get("NULL");
                                    if(oldValue != null){
                                        values = values + "'" + oldValue + "',";
                                    }else {
                                        values = values + "NULL,";
                                    }
                                }else{
                                    values = values + "NULL,";
                                }
                            } else if ("".equals(oldValue)) {
                                if (fieldType.startsWith("date") || fieldType.startsWith("time") || fieldType.startsWith("int")
                                        || fieldType.startsWith("double")) {
                                    values = values + "NULL,";
                                } else {
                                    values = values + "'" + oldValue + "',";
                                }
                            } else {
                                if(fieldType.startsWith("double")){
                                    oldValue = oldValue.replace(",","");
                                }
                                if (newField.getEnumMapper() != null && !newField.getEnumMapper().isEmpty()) {
                                    String enumoldValue = (String) newField.getEnumMapper().get(oldValue);
                                    if(enumoldValue != null && enumoldValue.contains("|")){
                                        multiValues = enumoldValue;
                                        oldValue = enumoldValue;
                                        multiFlag = true;
                                    }else if(enumoldValue != null){
                                        oldValue = enumoldValue;
                                    }
                                }
                                values = values + "'" + oldValue + "',";
                            }
                        }
                        if(multiFlag) {
                            String[] split = multiValues.split("\\|");
                            for (String s : split) {
                                String sqlValue = values.replace(multiValues,s);
                                bw.write(insertSQL + sqlValue.substring(0, sqlValue.lastIndexOf(",")) + ");" + "\n");
                            }
                        }else{
                            bw.write(insertSQL + values.substring(0, values.lastIndexOf(",")) + ");" + "\n");
                        }
                    }
                }
                // 重置 resultSet2 游标以便下一次循环
                resultSet2.beforeFirst();
            }
        }catch (Exception e){
            //hwLog.info("文件生成失败！\n" + e.getMessage());
        }
        // 关闭资源
        resultSet1.close();
        resultSet2.close();
    }

    /**
     * 多对一的表关系映射
     * @param statement1
     * @param mapperSheet
     * @param outputDirectory
     * @throws Exception
     */
    public void generateMultiSqlFile(Statement statement1, MapperSheet mapperSheet, String outputDirectory,String driverType,Charset exportCharacterSet) throws Exception{
        List<MapperTable> oldTableList = mapperSheet.getOldTableList();
        MapperTable newTable = mapperSheet.getNewTable();
        String whereCondition = mapperSheet.getWhereCondition();
        String oldTableFieldSql = mapperSheet.getOldTableFieldSql();
        String newTableFieldSql = mapperSheet.getNewTableFieldSql();
        List<MapperTableRelation> tableRelationList = mapperSheet.getTableRelationList();

        //查询旧表的全字段，区分左连接，右连接，内连接查询
        String oldTabNameSql = "";
        String selectQuerySize = "";
        String tableJoinType = mapperSheet.getTableJoinType();
        //2张表的连接查询，多表的还没考虑好 TODO
        if ("inner".equals(tableJoinType)) {
            for (MapperTable mapperTable : oldTableList) {
                oldTabNameSql += mapperTable.getTableName() + ",";
            }
            oldTabNameSql = oldTabNameSql.substring(0, oldTabNameSql.lastIndexOf(","));
            selectQuerySize = "SELECT count(1) as rowNum FROM " + oldTabNameSql + " " + whereCondition;
        } else if ("left".equals(tableJoinType) && oldTableList.size() == 2) {
            selectQuerySize = "SELECT count(1) as rowNum FROM " + oldTableList.get(0).getTableName() + " LEFT JOIN " + oldTableList.get(1).getTableName() + " ON " + whereCondition;
        } else if ("right".equals(tableJoinType) && oldTableList.size() == 2) {
            selectQuerySize = "SELECT count(1) as rowNum FROM " + oldTableList.get(0).getTableName() + " RIGHT JOIN " + oldTableList.get(1).getTableName() + " ON " + whereCondition;
        }
        //查询迁移总条数,控制文件个数
        ResultSet sizeResultSet = statement1.executeQuery(selectQuerySize);
        int pageNum = 0;
        int realRowNum = 0;
        while (sizeResultSet.next()){
            realRowNum = Integer.valueOf(sizeResultSet.getString("rowNum"));
            logger.info("查询数据总量：" + realRowNum);
            pageNum = (int) Math.ceil((double) realRowNum / LIMIT_QUERY_NUM);
            logger.info("将生成脚本文件个数：" + pageNum);
        }
        sizeResultSet.close();

        // 拼接生成文件内容sql前缀
        String insertSQL = "INSERT INTO " + newTable.getTableName() + " (" + newTableFieldSql + ") VALUES (";
        for (int r = 0; r < pageNum; r++) {
            int startNum = r * LIMIT_QUERY_NUM + 1;
            int endNum = Math.min((r + 1) * LIMIT_QUERY_NUM, realRowNum);
            // 创建SQL脚本文件
            String scriptFileName = r + newTable.getTableName() + "_script_" + (((r+1) == pageNum)?realRowNum:LIMIT_QUERY_NUM) + ".sql";
            File scriptFile = new File(outputDirectory, scriptFileName);
            // 执行查询旧表数据，生成插入SQL
            try(BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(scriptFile),exportCharacterSet))){
                //分页查询，避免单次数据过大，内存加载消耗过大
                String selectQuery = "";
                switch (driverType) {
                    case "db2":
                        if ("inner".equals(tableJoinType)) {
                            selectQuery = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY some_unique_column) as rownum, " + oldTableFieldSql + " FROM " + oldTabNameSql + " " + whereCondition + ") AS temp WHERE rownum BETWEEN " + startNum + " AND " + endNum;
                        } else if ("left".equals(tableJoinType)) {
                            selectQuery = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY some_unique_column) as rownum, " + oldTableFieldSql + " FROM " + oldTableList.get(0).getTableName() + " LEFT JOIN " + oldTableList.get(1).getTableName() + " ON " + whereCondition + ") AS temp WHERE rownum BETWEEN " + startNum + " AND " + endNum;
                        } else if ("right".equals(tableJoinType)){
                            selectQuery = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY some_unique_column) as rownum, " + oldTableFieldSql + " FROM " + oldTableList.get(0).getTableName() + " RIGHT JOIN " + oldTableList.get(1).getTableName() + " ON " + whereCondition + ") AS temp WHERE rownum BETWEEN " + startNum + " AND " + endNum;
                        }
                        break;
                    case "mysql":
                        if ("inner".equals(tableJoinType)) {
                            selectQuery = "SELECT " + oldTableFieldSql + " FROM " + oldTabNameSql + " " + whereCondition + " LIMIT " + LIMIT_QUERY_NUM + " OFFSET " + startNum;
                        } else if ("left".equals(tableJoinType)) {
                            selectQuery = "SELECT " + oldTableFieldSql + " FROM " + oldTableList.get(0).getTableName() + " LEFT JOIN " + oldTableList.get(1).getTableName() + " ON " + whereCondition + " LIMIT " + LIMIT_QUERY_NUM + " OFFSET " + startNum;
                        } else if ("right".equals(tableJoinType)){
                            selectQuery = "SELECT " + oldTableFieldSql + " FROM " + oldTableList.get(0).getTableName() + " RIGHT JOIN " + oldTableList.get(1).getTableName() + " ON " + whereCondition + " LIMIT " + LIMIT_QUERY_NUM + " OFFSET " + startNum;
                        }
                        break;
                    default:
                        break;
                }
                logger.info("查询语句：\n" + selectQuery);
                ResultSet resultSet = statement1.executeQuery(selectQuery);
                resultValueAssemble(resultSet,bw,insertSQL,mapperSheet);
                logger.info("生成文件成功!" + scriptFile);
            }
        }
    }

    /**
     * 一对一的表关系映射
     * @param statement
     * @param mapperSheet
     * @param outputDirectory
     * @throws Exception
     */
    public void generateSqlFile(Statement statement, MapperSheet mapperSheet, String outputDirectory, String driverType, Charset exportCharacterSet) throws Exception{
        //一对一
        MapperTable oldTable = mapperSheet.getOldTable();
        MapperTable newTable = mapperSheet.getNewTable();

        //获取表信息
        String whereCondition = mapperSheet.getWhereCondition();

        //查询旧表的数据量
        String selectQuerySize = "SELECT COUNT(1) as rowNum FROM " + oldTable.getTableName() + " " + whereCondition;

        // 拼接sql
        String insertSQL = "INSERT INTO " + newTable.getTableName() + " (" + newTable.toString() + ") VALUES (";

        //查询结果集数据量，用于分批导出
        ResultSet sizeResultSet = statement.executeQuery(selectQuerySize);

        int pageNum = 0;
        int realRowNum = 0;
        while (sizeResultSet.next()){
            realRowNum = Integer.valueOf(sizeResultSet.getString("rowNum"));
            if(realRowNum % LIMIT_QUERY_NUM == 0){
                pageNum = realRowNum / LIMIT_QUERY_NUM;
            }else{
                pageNum = realRowNum / LIMIT_QUERY_NUM + 1;
            }
        }
        sizeResultSet.close();
        //数据分批保存到多个文件
        for (int r = 0; r < pageNum; r++) {
            // 创建SQL脚本文件
            int startNum = r * LIMIT_QUERY_NUM;
            int endNum = startNum + LIMIT_QUERY_NUM;
            if(realRowNum > LIMIT_QUERY_NUM){
                realRowNum = realRowNum - (pageNum - 1) * LIMIT_QUERY_NUM;
            }
            String scriptFileName = r + oldTable.getTableName() + "_" + newTable.getTableName() + "_script_" + (((r+1) == pageNum)?realRowNum:LIMIT_QUERY_NUM) + ".sql";
            File scriptFile = new File(outputDirectory, scriptFileName);
            String selectQuery = "";
            //分页查询，避免单次数据过大，内存加载消耗过大
            switch (driverType){
                case "db2":
                    selectQuery = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY some_unique_column) as rownum "+ oldTable.getTableName() + ".* FROM " + oldTable.getTableName() + " " + whereCondition + ") AS temp WHERE rownum BETWEEN " + startNum + " AND " + endNum;
                    break;
                case "mysql":
                    selectQuery = "SELECT * FROM " + oldTable.getTableName() + " " + whereCondition + " LIMIT " + startNum + "," + LIMIT_QUERY_NUM;
                    break;
                default:
                    break;
            }
            logger.info("数据库查询语句:\n" + selectQuery);
            try(BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(scriptFile),exportCharacterSet))){
                // 执行查询旧表数据，生成插入SQL
                ResultSet resultSet = statement.executeQuery(selectQuery);
                resultValueAssemble(resultSet,bw,insertSQL,mapperSheet);
                resultSet.close();
            }catch (Exception e){
                logger.error("文件生成失败!" + e.getMessage());
            }
            logger.info("生成文件成功!" + scriptFile);
        }
    }

    /**
     * 数据库
     * @param resultSet
     * @param bw
     * @param insertSQL
     * @param mapperSheet
     * @throws Exception
     */
    private void resultValueAssemble(ResultSet resultSet,BufferedWriter bw,String insertSQL,MapperSheet mapperSheet) throws Exception{
        boolean multiTable = mapperSheet.isMultiTable();
        MapperTable oldTable = mapperSheet.getOldTable();
        MapperTable newTable = mapperSheet.getNewTable();
        List<MapperTableRelation> tableRelationList = mapperSheet.getTableRelationList();
        Integer sheetLineNum = mapperSheet.getSheetLineNum();
        if(multiTable){
            sheetLineNum = mapperSheet.getTableRelationList().size();
        }
        while (resultSet.next()) {
            String values = "";

            for (int i = 0; i < sheetLineNum; i++) {
                MapperField newField;
                MapperField oldField;
                if(multiTable){
                    newField = tableRelationList.get(i).getNewField();
                    oldField = tableRelationList.get(i).getOldField();
                }else{
                    newField = newTable.getTableFields().get(i);
                    oldField = oldTable.getTableFields().get(i);
                }
                String specialCondition = newField.getSpecialCondition();
                String relRelation = newField.getRelRelation();
                boolean emptyFlag = newField.isEmptyFlag();

                if(HwUtil.isNullOrEmpty(newField.getFieldName())){
                    //无对应关系
                    continue;
                }else if(HwUtil.isNullOrEmpty(oldField.getFieldName())){
                    //判断是否存在 关联关系和特殊赋值
                    String value = null;
                    if(!HwUtil.isNullOrEmpty(relRelation)){
                        value = mapperRuleService.relRelationParse(relRelation, resultSet,emptyFlag);
                    }else if(!HwUtil.isNullOrEmpty(specialCondition)){
                        value = mapperRuleService.specialConditionParse(relRelation,emptyFlag);
                    }
                    values = values + value;
                }else{
                    //一对一的关系
                    //获取旧数据库中字段的值 新表对应值的类型
                    String oldValue = resultSet.getString(oldField.getFieldName());

                    if(!HwUtil.isNullOrEmpty(newField.getEnumMapper())){
                        oldValue = mapperRuleService.mapperFieldParse(oldValue,newField.getEnumMapper(),emptyFlag);
                    }else if(!HwUtil.isNullOrEmpty(relRelation)){
                        oldValue = mapperRuleService.relRelationParse(relRelation, resultSet,emptyFlag);
                    }else if(!HwUtil.isNullOrEmpty(specialCondition)){
                        oldValue = mapperRuleService.specialConditionParse(specialCondition,emptyFlag);
                    }else{
                        oldValue = mapperRuleService.emptyValueParse(emptyFlag,oldValue);
                    }
                    values = values + oldValue;
                }
            }
            if(mapperRuleService.isMultiLineFlag()){
                String[] split = mapperRuleService.getMultiLineValues().split("\\|");
                for (String s : split) {
                    String sqlValue = values.replace(mapperRuleService.getMultiLineValues(),s);
                    bw.write(insertSQL + sqlValue.substring(0,sqlValue.lastIndexOf(",")) + ");" + "\n");
                }
            }else{
                bw.write(insertSQL + values.substring(0, values.lastIndexOf(",")) + ");" + "\n");
            }
        }
    }
}
